<?php
namespace phiction;
use \phiction\exceptions\unimplemented;
use \phiction\iterator_utils as iter;
use \phiction\string_utils as str;
use \phiction\array_utils as arr;

class query_builder
{
    private $ld;
    private $pdo;

    private $mode;
    private $table;
    private $next_id;
    private $alias_from_desc;
    private $table_from_alias;
    private $fields;
    private $joins;
    private $count;
    private $offset;
    private $orders;
    private $wheres;
    private $inserts;
    private $updates;

    function __construct(schema_loader $ld, \PDO $pdo)
    {
        $pdo->setAttribute(\PDO::ATTR_ERRMODE, \PDO::ERRMODE_EXCEPTION);
        $this->ld = $ld;
        $this->pdo = $pdo;
        $this->reset();
    }

    function load(string $module_name)
    {
        $this->ld->load($module_name);
        return $this;
    }

    function reset()
    {
        $this->mode = null;
        $this->table = null;
        $this->next_id = 0;
        $this->alias_from_desc = [];
        $this->table_from_alias = [];
        $this->fields = null;
        $this->joins = [];
        $this->count = null;
        $this->offset = 0;
        $this->orders = null;
        $this->wheres = [];
        $this->inserts = [];
        $this->updates = null;
        return $this;
    }

    function run()
    {
        $this->require_mode();

        $plan = $this->plan();
        $values = $plan['values'];
        $plan = $plan['plan'];

        $result = null;
        $stmt = $this->run_sql($plan, $values);
        if ($this->mode === 'select') {
            $result = $stmt->fetchAll(\PDO::FETCH_ASSOC);
            if ($this->count === 0) {
                if (count($result) === 0) $result = null;
                else $result = $result[0];
            }
        } else {
            $result = $stmt->rowCount();
        }

        $this->reset();
        return $result;
    }

    # initiator
    // $id: int | null
    function table(string $name, $id=null)
    {
        if (!is_null($this->table)) throw new \LogicException("cannot call table() a second time before reset()");
        $name = $this->ld->sanitize_name($name);
        $this->ld->schema($name);   // ensure schema exists
        $this->table = $name;

        $alias = $this->generate_id();
        $this->alias_from_desc[''] = $alias;
        $this->table_from_alias[$alias] = $name;

        if (is_int($id)) $this->where([$this->identity(), $id]);
        elseif (!is_null($id)) throw new \InvalidArgumentException("id can only be null or int");
        $this->order(">" . $this->identity());

        return $this;
    }

    # parameter specifier
    function field(string ...$field_descriptors)
    {
        if (count($field_descriptors) === 0) throw new \InvalidArgumentException("must be at least 1 field descriptor for field()");
        $this->require_table();
        if (!is_null($this->fields)) throw new \LogicException("cannot call field() a second time before reset()");

        $fds = $this->desugar_field_descriptors($field_descriptors);
        $fds = $this->validate_field_descriptors($fds);
        $fds = $this->linearize_fds_refs($fds);
        $this->validate_field_unique($fds);
        $this->fields = $fds;

        return $this;
    }

    function where(array ...$predicates)
    {
        if (count($predicates) === 0) throw new \InvalidArgumentException("must be at least 1 predicate for where()");
        $this->require_table();

        $preds = $this->desugar_predicates($predicates);
        $this->wheres[] = $preds;

        return $this;
    }

    function order(string ...$field_descriptors_with_dir)
    {
        $this->require_table();

        if (count($field_descriptors_with_dir) === 0) {
            $this->orders = null;
            return $this;
        }

        # extract directions
        $dirs = [];
        foreach ($field_descriptors_with_dir as &$field) {
            $sigil = substr($field, 0, 1);
            if ($sigil === '>') {
                $dirs[] = "DESC";
                $field = substr($field, 1);
            }
            else if ($sigil === '<') {
                $dirs[] = "ASC";
                $field = substr($field, 1);
            }
            else {
                $dirs[] = "ASC";
            }
        }

        # desugar fds
        $fds = $this->desugar_field_descriptors($field_descriptors_with_dir);
        $fds = $this->validate_field_descriptors($fds, true);
        $fds = $this->linearize_fds_refs($fds);

        # merge in directions
        foreach ($fds as $i => &$fd)
            $fd['dir'] = $dirs[$i];
        $this->orders = $fds;

        return $this;
    }

    # mode switcher
    # page starts from 1.
    function select($count=null, int $page=1)
    {
        $this->switch_mode('select');

        if (is_int($count)) {
            if ($count < 0) throw new \InvalidArgumentException("count cannot be negative: $count");
            if (is_null($this->count) || $count < $this->count)
                $this->count = $count;
        }
        else if (!is_null($count)) throw new \InvalidArgumentException("invalid count: $count");

        $offset = (is_int($count) ? $count : 0) * ($page - 1);
        if ($offset < 0) $offset = 0;
        $this->offset = $offset;

        return $this;
    }

    function insert(...$values)
    {
        $this->switch_mode('insert');

        $this->match_fields($this->fields, $values);
        $this->inserts[] = $values;

        return $this;
    }

    function update(...$values)
    {
        $this->switch_mode('update');
        if (!is_null($this->updates)) throw new \LogicException("cannot call update() a second time before reset()");

        $this->match_fields($this->fields, $values);
        $this->updates = $values;

        return $this;
    }

    function delete()
    {
        if ($this->mode === 'delete') throw new \LogicException("cannot call delete() a second time before reset()");
        $this->switch_mode('delete');

        return $this;
    }

    # helpers
    function identity($schema_name=null): string
    {
        if (is_null($schema_name)) {
            if (is_null($this->table)) throw new \InvalidArgumentException("need to call table() before calling identity()");
            $schema_name = $this->table;
        }
        else if (!is_string($schema_name)) throw new \InvalidArgumentException("string expected, but got: " . var_export($schema_name, true));
        return $this->ld->identity_field($schema_name);
    }

    private function run_sql(string $plan, array $values): \PDOStatement
    {
        $stmt = $this->pdo->prepare($plan);
        $stmt->execute($values);
        return $stmt;
    }

    private function generate_id()
    {
        $id = $this->next_id++;
        return "_t$id";
    }

    /*
        ('' => '_t0')
        reply.who.login => join(['reply', 'who'])

        ('reply' => '_t1')
        JOIN comment _t1 ON _t1.id = _t0.reply
            foreign_table => comment
            foreign_alias => _t1
            local_alias   => _t0
            local_field   => reply
            _foreign_desc => 'reply'
            _local_desc   => ''
            _local_table  => 'comment'

        ('reply.who' => '_t2')
        JOIN user _t2 ON _t2.id = _t1.who
            foreign_table => user
            foreign_alias => _t2
            local_alias   => _t1
            local_field   => who
            _foreign_desc => 'reply.who'
            _local_desc   => 'reply'
            _local_table  => 'comment'
    */
    private function join(array $refs)
    {
        $local_desc_parts = [];
        foreach ($refs as $ref) {
            $local_desc = implode('.', $local_desc_parts);
            $local_alias = $this->alias_from_desc[$local_desc];
            $local_table = $this->table_from_alias[$local_alias];
            $local_field = $ref;

            $local_desc_parts[] = $ref;
            $foreign_desc = implode('.', $local_desc_parts);
            if (array_key_exists($foreign_desc, $this->alias_from_desc))
                continue;

            $foreign_alias = $this->generate_id();
            $foreign_table = $this->ld->schemas[$local_table][$local_field]['name'];

            $this->alias_from_desc[$foreign_desc] = $foreign_alias;
            $this->table_from_alias[$foreign_alias] = $foreign_table;
            $this->joins[] = [
                'foreign-table' => $foreign_table,
                'foreign-alias' => $foreign_alias,
                'local-alias'   => $local_alias,
                'local-field'   => $local_field,
            ];
        }
    }

    private function require_table()
    {
        if (is_null($this->table)) throw new \LogicException("must call table() first");
    }

    private function require_mode()
    {
        if ($this->no_mode()) throw new \LogicException("must call select(), insert(), update() or delete() first");
    }


    # mode management
    private function switch_mode(string $mode)
    {
        $this->require_table();

        if ($this->no_mode()) {
            $this->mode = $mode;
            return;
        }

        if ($this->mode === $mode) return;

        throw new \InvalidArgumentException("cannot switch from \"$this->mode\" to \"$mode\"");
    }

    private function no_mode(): bool
    {
        return is_null($this->mode);
    }


    # generate execution plan
    // returns ['plan' => string, 'values' => [...]]
    function plan(): array
    {
        $this->require_mode();
        switch ($this->mode) {
            case 'select': return $this->select_plan();
            case 'insert': return $this->insert_plan();
            case 'update': return $this->update_plan();
            case 'delete': return $this->delete_plan();
            default: throw new unimplemented("$this->mode mode");
        }
    }

    // returns ['plan' => string, 'values' => [...]]
    private function select_plan(): array
    {
        $plan = [];
        $values = [];

        $fields = $this->fields_plan();
        $plan[] = "SELECT $fields";

        $table = $this->ld::sql_name($this->table);
        $alias = $this->ld::sql_name($this->alias_from_desc['']);
        $plan[] = "FROM $table $alias";

        $plan[] = $this->joins_plan();

        if (count($this->wheres) !== 0) {
            $wheres = $this->wheres_plan();
            $wheres_plan = $wheres['plan'];
            $wheres_values = $wheres['values'];
            $plan[] = "WHERE $wheres_plan";
            $values[] = $wheres_values;
        }

        if (!is_null($this->orders)) {
            $orders = $this->orders_plan();
            $plan[] = "ORDER BY $orders";
        }

        if (!is_null($this->count)) {
            $count = $this->count;
            if ($count === 0) $count = 1;
            $plan[] = "LIMIT $count";
        }

        if ($this->offset !== 0)
            $plan[] = "OFFSET $this->offset";

        $plan = implode("\n", $plan) . ";\n";
        $values = iter::flatten_array($values);
        return [
            'plan' => $plan,
            'values' => $values,
        ];
    }

    // returns ['plan' => string, 'values' => [...]]
    private function insert_plan(): array
    {
        if (count($this->wheres) !== 0) throw new \LogicException("where() does not make sense with insert()");
        if (count($this->joins) !== 0) throw new \LogicException("joins does not make sense with insert()");

        $value_template = implode(', ', array_fill(0, count($this->fields), '?'));
        $value_template = str::indent("($value_template)");
        $value_template = implode(",\n", array_fill(0, count($this->inserts), $value_template));

        $fields = [];
        foreach ($this->fields as $fd)
            $fields[] = $this->ld::sql_name($fd['field']);
        $fields = implode(', ', $fields);

        $table = $this->ld::sql_name($this->table);
        $plan = "INSERT INTO $table($fields) VALUES\n$value_template;\n";
        $values = iter::flatten_array($this->inserts);

        return [
            'plan' => $plan,
            'values' => $values,
        ];
    }

    // returns ['plan' => string, 'values' => [...]]
    private function update_plan(): array
    {
        $plan = [];
        $values = [];

        $table = $this->ld::sql_name($this->table);
        $alias = $this->ld::sql_name($this->alias_from_desc['']);
        $plan[] = "UPDATE $table $alias";

        $fields = [];
        $alias = $this->alias_from_desc[''];
        foreach ($this->fields as $fd) {
            $refs = $fd['refs'];
            if ($refs !== $alias) {
                $foreign_table = $this->ld::sql_name($this->table_from_alias[$refs]);
                throw new \LogicException("cannot change field in table $foreign_table when updating table $table");
            }
            $fields[] = $this->ld::sql_name($fd['field']);
        }
        $fields = implode(', ', $fields);

        $value_template = implode(', ', array_fill(0, count($this->fields), '?'));
        $plan[] = "SET ($fields) = ($value_template)";
        $values[] = $this->updates;

        $where = null;
        if (count($this->wheres) !== 0) {
            $wheres = $this->wheres_plan();
            $where = $wheres['plan'];
            $values[] = $wheres['values'];
        }

        if (count($this->joins) === 0) {
            if (!is_null($where))
                $plan[] = "WHERE $where";
        } else {
            $fj = $this->from_joins_plan();
            $from = $fj['from'];
            $fj_where = $fj['where'];
            $plan[] = "FROM $from";
            if (is_null($where)) {
                $plan[] = "WHERE $fj_where";
            } else {
                $plan[] = "WHERE $where";
                $plan[] = "AND $fj_where";
            }
        }

        $plan = implode("\n", $plan) . ";\n";
        $values = iter::flatten_array($values);
        return [
            'plan' => $plan,
            'values' => $values,
        ];
    }

    // returns ['plan' => string, 'values' => [...]]
    private function delete_plan(): array
    {
        if (count($this->fields) !== 0) throw new \LogicException("field() does not make sense with delete()");

        $plan = [];
        $values = [];

        $table = $this->ld::sql_name($this->table);
        $alias = $this->ld::sql_name($this->alias_from_desc['']);
        $plan[] = "DELETE FROM $table $alias";

        $where = null;
        if (count($this->wheres) !== 0) {
            $wheres = $this->wheres_plan();
            $where = $wheres['plan'];
            $values[] = $wheres['values'];
        }

        if (count($this->joins) === 0) {
            if (!is_null($where))
                $plan[] = "WHERE $where";
        } else {
            $fj = $this->from_joins_plan();
            $from = $fj['from'];
            $fj_where = $fj['where'];
            $plan[] = "USING $from";
            if (is_null($where)) {
                $plan[] = "WHERE $fj_where";
            } else {
                $plan[] = "WHERE $where";
                $plan[] = "AND $fj_where";
            }
        }

        $plan = implode("\n", $plan) . ";\n";
        $values = iter::flatten_array($values);
        return [
            'plan' => $plan,
            'values' => $values,
        ];
    }

    private function fields_plan(): string
    {
        $plan = [];
        foreach ($this->fields as $field) {
            $table_alias = $this->ld::sql_name($field['refs']);
            $table_field = $this->ld::sql_name($field['field']);

            if (is_null($field['alias'])) {
                $plan[] = "$table_alias.$table_field";
            } else {
                $field_alias = $this->ld::sql_name($field['alias']);
                $plan[] = "$table_alias.$table_field $field_alias";
            }
        }
        return implode(', ', $plan);
    }

    private function joins_plan(): string
    {
        $plan = [];
        foreach ($this->joins as $join) {
            $foreign_table = $this->ld::sql_name($join['foreign-table']);
            $foreign_alias = $this->ld::sql_name($join['foreign-alias']);
            $foreign_field = $this->ld::sql_name($this->identity($join['foreign-table']));
            $local_alias = $this->ld::sql_name($join['local-alias']);
            $local_field = $this->ld::sql_name($join['local-field']);
            $plan[] = "LEFT JOIN $foreign_table $foreign_alias ON $foreign_alias.$foreign_field = $local_alias.$local_field";
        }
        return implode("\n", $plan);
    }

    // returns ['from' => string, 'where' => string]
    private function from_joins_plan(): array
    {
        $from = [];
        $where = [];
        foreach ($this->joins as $join) {
            $foreign_table = $this->ld::sql_name($join['foreign-table']);
            $foreign_alias = $this->ld::sql_name($join['foreign-alias']);
            $foreign_field = $this->ld::sql_name($this->identity($join['foreign-table']));
            $local_alias = $this->ld::sql_name($join['local-alias']);
            $local_field = $this->ld::sql_name($join['local-field']);
            $from[] = "$foreign_table $foreign_alias";
            $where[] = "$foreign_alias.$foreign_field = $local_alias.$local_field";
        }
        $from = implode(', ', $from);
        $where = implode(' AND ', $where);
        return [
            'from' => $from,
            'where' => $where,
        ];
    }

    private function orders_plan(): string
    {
        $plan = [];
        foreach ($this->orders as $order) {
            $table_alias = $this->ld::sql_name($order['refs']);
            $table_field = $this->ld::sql_name($order['field']);
            $dir = $order['dir'];
            $plan[] = "$table_alias.$table_field $dir";
        }
        return implode(", ", $plan);
    }

    // returns ['plan' => string, 'values' => [...]]
    private function wheres_plan(): array
    {
        $plan = [];
        $values = [];
        foreach ($this->wheres as $preds) {
            $p = $this->preds_plan($preds);
            if (count($preds) > 1) $p['plan'] = '(' . $p['plan'] . ')';
            $plan[] = $p['plan'];
            $values[] = $p['values'];
        }
        $plan = implode(" AND ", $plan);
        $values = iter::flatten_array($values);
        return [
            'plan' => $plan,
            'values' => $values,
        ];
    }

    // returns ['plan' => string, 'values' => [...]]
    private function preds_plan(array $preds): array
    {
        $plan = [];
        $values = [];
        foreach ($preds as $pred) {
            $p = $this->pred_plan($pred);
            $plan[] = $p['plan'];
            $values[] = $p['value'];
        }
        $plan = implode(" OR ", $plan);
        return [
            'plan' => $plan,
            'values' => $values,
        ];
    }

    // returns ['plan' => string, 'value' => ...]
    private function pred_plan(array $pred): array
    {
        $fd = $pred[0];
        $value = $pred[1];
        $op = $pred[2];
        $table_alias = $this->ld::sql_name($fd['refs']);
        $table_field = $this->ld::sql_name($fd['field']);
        $plan = "$table_alias.$table_field $op ?";
        return [
            'plan' => $plan,
            'value' => $value,
        ];
    }


    # desugar
    function desugar_field_descriptors(array $fds): array
    {
        foreach ($fds as &$fd)
            $fd = $this->desugar_field_descriptor($fd);
        return $fds;
    }

    /*
        example:
            'author'                     => ['refs' => []                , 'field' => 'author', 'alias' => null]
            'author.name'                => ['refs' => ['author']        , 'field' => 'name'  , 'alias' => null]
            'post.author.login username' => ['refs' => ['post', 'author'], 'field' => 'login' , 'alias' => 'username']

        fields:
            refs
            field
            alias
    */
    function desugar_field_descriptor(string $fd): array
    {
        if ($fd === '') throw new \InvalidArgumentException("field descriptor cannot be an empty string");

        # extract alias
        $parts = explode(' ', $fd);
        $alias = null;
        $field = null;
        switch (count($parts)) {
            case 2:
                $alias = $this->ld->sanitize_name($parts[1]);
                // fall through intensionally
            case 1:
                $field = $parts[0];
                break;
            default:
                throw new \InvalidArgumentException("field descriptor can only have 2 parts: $fd");
        }

        # split field and refs
        $refs = explode('.', $field);
        foreach ($refs as &$ref)
            $ref = $this->ld->sanitize_name($ref);
        $field = array_pop($refs);

        return [
            'refs' => $refs,
            'field' => $field,
            'alias' => $alias,
        ];
    }

    function validate_field_descriptors(array $fds, bool $no_alias=false)
    {
        foreach ($fds as &$fd)
            $fd = $this->validate_field_descriptor($fd, $no_alias);
        return $fds;
    }

    function validate_field_descriptor(array $fd, bool $no_alias=false): array
    {
        if ($no_alias && !is_null($fd['alias'])) {
            $desc = implode('.', array_merge($fd['refs'], [$fd['field']]));
            $alias = $fd['alias'];
            throw new \InvalidArgumentException("alias not allowed on $desc: $alias");
        }
        $table = $this->table;
        foreach ($fd['refs'] as $ref) {
            $type = $this->ld->schema_field_type($table, $ref);
            $tt = $type['type'];
            if ($tt !== 'table') throw new \InvalidArgumentException("$table.$ref is not a table: $tt");
            $table = $type['name'];
        }
        $fd['type'] = $this->ld->schema_field_type($table, $fd['field']);
        return $fd;
    }

    function linearize_fds_refs(array $fds): array
    {
        foreach ($fds as &$fd)
            $fd = self::linearize_fd_refs($fd);
        return $fds;
    }

    function linearize_fd_refs(array $fd): array
    {
        $desc = implode('.', $fd['refs']);
        if (!array_key_exists($desc, $this->alias_from_desc))
            $this->join($fd['refs']);
        $fd['refs'] = $this->alias_from_desc[$desc];
        return $fd;
    }

    function desugar_predicates(array $preds): array
    {
        foreach ($preds as &$pred)
            $pred = $this->desugar_predicate($pred);
        return $preds;
    }

    function desugar_predicate(array $pred): array
    {
        $num = count($pred);
        if ($num < 2) {
            $pred = var_export($pred, true);
            throw new \InvalidArgumentException("must have at least 2 parts, got $num: $pred");
        }
        if ($num > 3) {
            $pred = var_export($pred, true);
            throw new \InvalidArgumentException("can have at most 3 parts, got $num: $pred");
        }

        $fd = $this->desugar_field_descriptor($pred[0]);
        $fd = $this->validate_field_descriptor($fd, true);
        $this->match_type($fd['type'], $pred[1]);
        $fd = $this->linearize_fd_refs($fd);
        $pred[0] = $fd;

        if ($num === 2) $pred[] = '=';

        return $pred;
    }

    private function match_fields(array $fds, array &$values)
    {
        $num_fds = count($fds);
        $num_values = count($values);
        if ($num_fds !== $num_values) throw new \InvalidArgumentException("number of values ($num_values) does not fit the number of fields ($num_fds)");
        foreach ($fds as $i => $fd)
            $this->match_type($fd['type'], $values[$i]);
    }

    private function match_type(array $type, &$value)
    {
        if (is_null($value)) {
            if ($type['nullable']) return;
            throw new \InvalidArgumentException("value cannot be null, expect: " . $type['type']);
        }

        switch ($type['type']) {
            case 'identity':
                if (is_int($value)) break;
                throw new \InvalidArgumentException("type mismatch, expect identity(int), but got: " . var_export($value, true));
            case 'int':
                if (is_int($value)) break;
                throw new \InvalidArgumentException("type mismatch, expect int, but got: " . var_export($value, true));
            case 'string':
                if (!is_string($value))
                    throw new \InvalidArgumentException("type mismatch, expect string, but got: " . var_export($value, true));
                $max_len = $type['max-length'];
                if (!is_null($max_len) && strlen($value) > $max_len)
                    throw new \InvalidArgumentException("string too long, at most $max_len allowed");
                break;
            case 'datetime':
                if (!($value instanceof \DateTime))
                    throw new \InvalidArgumentException("type mismatch, expect DateTime, but got: " . var_export($value, true));
                $value = $value->format("Y-m-d H:i:s");
                break;
            case 'bool':
                if (!is_bool($value))
                    throw new \InvalidArgumentException("type mismatch, expect bool, but got: " . var_export($value, true));
                $value = (int)$value;   // cast to int to prevent bugs in PHP PDO PostgreSQL Driver.
                break;
            case 'table':
                if (is_int($value)) break;
                throw new \InvalidArgumentException("type mismatch, expect table(int), but got: " . var_export($value, true));
            case 'enum':
                if (!is_string($value))
                    throw new \InvalidArgumentException("type mismatch, expect a string for enum, but got: " . var_export($value, true));
                $enum = $type['enum'];
                if (!arr::has_key($enum, $value)) {
                    $name = $type['name'];
                    $enum = implode(", ", array_keys($enum));
                    throw new \InvalidArgumentException("unexpected '$value' for enum '$name', expect one of ($enum)");
                }
                break;
            case 'sequence':
                throw new unimplemented("sequence type matching");
            default:
                throw new \InvalidArgumentException("no such type: " . $type['type']);
        }
    }

    private function validate_field_unique($fds)
    {
        $names = [];
        foreach ($fds as $fd) {
            $alias = $fd['alias'];
            $field = $fd['field'];
            $name = is_null($alias) ? $field : $alias;
            if (array_key_exists($name, $names))
                throw new \InvalidArgumentException("duplicate output name: $name");
            $names[$name] = true;
        }
    }
}

